package com.lunker.moneyrecord;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class MyDatabaseOpenHelper extends SQLiteOpenHelper {
	
	private Context context = null;
	private static final int DATABASE_VERSION = 6;
	private static final String ACCOUNT_BOOK_DB_NAME = "account_book";
	private static final String ACCOUNT_BOOK_TABLE_NAME = "money_record";
	private static final String CATEGORY_TABLE_NAME = "category";
	
//	private static final String ACCOUNT_BOOK_TABLE_CREATE_V2 = "CREATE TABLE " + 
//			ACCOUNT_BOOK_TABLE_NAME + 
//			" (rid INTEGER PRIMARY KEY AUTOINCREMENT, year INTEGER, " +  
//			"month INTEGER, day INTEGER, amount INTEGER);";
	
//	private static final String ACCOUNT_BOOK_TABLE_CREATE_V3 = "CREATE TABLE " +
//			ACCOUNT_BOOK_TABLE_NAME + 
//				" (rid INTEGER PRIMARY KEY, year INTEGER, month INTEGER, " +
//					"day INTEGER, amount REAL);";
	
//	private static final String ACCOUNT_BOOK_TABLE_CREATE_V4 = "CREATE TABLE " +
//			ACCOUNT_BOOK_TABLE_NAME + 
//			" (rid INTEGER PRIMARY KEY, year INTEGER, month INTEGER, " +
//			"day INTEGER, amount REAL, catalog INTEGER);";
	
//	private static final String ACCOUNT_BOOK_TABLE_CREATE_V5 = "CREATE TABLE " +
//			ACCOUNT_BOOK_TABLE_NAME + 
//			" (rid INTEGER PRIMARY KEY, year INTEGER, month INTEGER, " +
//			"day INTEGER, amount REAL, category INTEGER);";
	
	private static final String ACCOUNT_BOOK_TABLE_CREATE_V6 = "CREATE TABLE " +
			ACCOUNT_BOOK_TABLE_NAME + 
			" (rid INTEGER PRIMARY KEY, year INTEGER, month INTEGER, " +
			"day INTEGER, amount REAL, category INTEGER, comment text);";
	
	private static final String CATALOG_CREATE_V5 = "CREATE TABLE " +
			CATEGORY_TABLE_NAME + " (cid INTEGER UNIQUE, cname TEXT);";
	
	public MyDatabaseOpenHelper (Context context) {
		super(context, ACCOUNT_BOOK_DB_NAME, null, DATABASE_VERSION);
		this.context = context;
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		//Main table
		db.execSQL(ACCOUNT_BOOK_TABLE_CREATE_V6);
		//Catalog table
		createCatalogTable(db);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		if (oldVersion < 3) {
			db.execSQL("ALTER TABLE money_record RENAME TO money_record_old;");
			db.execSQL("CREATE TABLE money_record " +
					"(rid INTEGER PRIMARY KEY, year INTEGER, month INTEGER, " +
					"day INTEGER, amount REAL);");
			db.execSQL("INSERT INTO money_record " +
					"select rid, year, month, day, amount from money_record_old;");
			db.execSQL("DROP TABLE money_record_old;");
		}
		
		if (oldVersion < 4) {
			db.execSQL("ALTER TABLE money_record ADD COLUMN catalog INTEGER;");
			createCatalogTable(db);
		}
		
		if (oldVersion < 5) {
			//Rename column catalog to category.
			db.execSQL("ALTER TABLE money_record RENAME TO money_record_old;");
			db.execSQL("CREATE TABLE money_record " +
					"(rid INTEGER PRIMARY KEY, year INTEGER, month INTEGER, " +
					"day INTEGER, amount REAL, category INTEGER);");
			db.execSQL("INSERT INTO money_record " +
					"select rid, year, month, day, amount, catalog from money_record_old;");
			db.execSQL("DROP TABLE money_record_old;");
			//Rename table catalog to category.
			db.execSQL("DROP TABLE catalog;");
			createCatalogTable(db);
		}
		
		if (oldVersion < 6) {
			db.execSQL("ALTER TABLE money_record ADD COLUMN comment text");
		}
	}

	private void createCatalogTable (SQLiteDatabase db) {
		//Create table.
		db.execSQL(CATALOG_CREATE_V5);
		//Insert catalogs.
		int[] ids = context.getResources().getIntArray(R.array.category_id);
		String[] names = context.getResources().getStringArray(R.array.category_name);
		
		for (int i = 0; i < ids.length; i++) {
			db.execSQL("INSERT INTO category (cid, cname) values (?, ?);",
					new String[] {
						Integer.toString(ids[i]),
						names[i]
					}
			);
		}
	}
}
